package com.warom.sdg.dao;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import com.warom.sdg.database.SdgDatabase;
import com.warom.sdg.model.GrainSituationEntity;

import java.util.ArrayList;
import java.util.List;

/**
 * 粮情数据访问对象实现类
 */
public class GrainSituationDao_Impl implements GrainSituationDao {
    private static final String TAG = "GrainSituationDao_Impl";
    
    private final SdgDatabase database;
    
    /**
     * 构造函数
     * @param database 数据库实例
     */
    public GrainSituationDao_Impl(SdgDatabase database) {
        this.database = database;
    }
    
    @Override
    public long insert(GrainSituationEntity grainSituation) {
        SQLiteDatabase db = database.getWritableDatabase();
        ContentValues values = entityToContentValues(grainSituation);
        return db.insert(SdgDatabase.TABLE_GRAIN_SITUATION, null, values);
    }
    
    @Override
    public List<Long> insertAll(List<GrainSituationEntity> grainSituations) {
        SQLiteDatabase db = database.getWritableDatabase();
        List<Long> ids = new ArrayList<>();
        db.beginTransaction();
        try {
            for (GrainSituationEntity entity : grainSituations) {
                ContentValues values = entityToContentValues(entity);
                long id = db.insert(SdgDatabase.TABLE_GRAIN_SITUATION, null, values);
                ids.add(id);
            }
            db.setTransactionSuccessful();
        } finally {
            db.endTransaction();
        }
        return ids;
    }
    
    @Override
    public void update(GrainSituationEntity grainSituation) {
        SQLiteDatabase db = database.getWritableDatabase();
        ContentValues values = entityToContentValues(grainSituation);
        db.update(SdgDatabase.TABLE_GRAIN_SITUATION, values, SdgDatabase.COLUMN_ID + " = ?",
                new String[]{String.valueOf(grainSituation.getId())});
    }
    
    @Override
    public void delete(GrainSituationEntity grainSituation) {
        SQLiteDatabase db = database.getWritableDatabase();
        db.delete(SdgDatabase.TABLE_GRAIN_SITUATION, SdgDatabase.COLUMN_ID + " = ?",
                new String[]{String.valueOf(grainSituation.getId())});
    }
    
    @Override
    public List<GrainSituationEntity> getGrainSituationByTimeRange(long startTime, long endTime) {
        SQLiteDatabase db = database.getReadableDatabase();
        String selection = SdgDatabase.COLUMN_TIMESTAMP + " BETWEEN ? AND ?";
        String[] selectionArgs = {String.valueOf(startTime), String.valueOf(endTime)};
        Cursor cursor = db.query(SdgDatabase.TABLE_GRAIN_SITUATION, null, selection, selectionArgs, null, null, SdgDatabase.COLUMN_TIMESTAMP + " DESC");
        return cursorToList(cursor);
    }
    
    @Override
    public List<GrainSituationEntity> getGrainSituationByCableAndTimeRange(int cableNo, long startTime, long endTime) {
        SQLiteDatabase db = database.getReadableDatabase();
        String selection = SdgDatabase.COLUMN_CABLE_NO + " = ? AND " + SdgDatabase.COLUMN_TIMESTAMP + " BETWEEN ? AND ?";
        String[] selectionArgs = {String.valueOf(cableNo), String.valueOf(startTime), String.valueOf(endTime)};
        Cursor cursor = db.query(SdgDatabase.TABLE_GRAIN_SITUATION, null, selection, selectionArgs, null, null, SdgDatabase.COLUMN_TIMESTAMP + " DESC");
        return cursorToList(cursor);
    }
    
    @Override
    public List<GrainSituationEntity> getGrainSituationByCableNameAndTimeRange(int cableNo, String cableName, long startTime, long endTime) {
        SQLiteDatabase db = database.getReadableDatabase();
        String selection = SdgDatabase.COLUMN_CABLE_NO + " = ? AND " + "cable_name" + " = ? AND " + SdgDatabase.COLUMN_TIMESTAMP + " BETWEEN ? AND ?";
        String[] selectionArgs = {String.valueOf(cableNo), cableName, String.valueOf(startTime), String.valueOf(endTime)};
        Cursor cursor = db.query(SdgDatabase.TABLE_GRAIN_SITUATION, null, selection, selectionArgs, null, null, SdgDatabase.COLUMN_TIMESTAMP + " DESC");
        return cursorToList(cursor);
    }
    
    @Override
    public List<GrainSituationEntity> getLatestGrainSituation() {
        SQLiteDatabase db = database.getReadableDatabase();
        String query = "SELECT * FROM " + SdgDatabase.TABLE_GRAIN_SITUATION + " WHERE (" + SdgDatabase.COLUMN_CABLE_NO + ", " + "cable_name" + ", " + SdgDatabase.COLUMN_TIMESTAMP + ") IN " +
                "(SELECT " + SdgDatabase.COLUMN_CABLE_NO + ", " + "cable_name" + ", MAX(" + SdgDatabase.COLUMN_TIMESTAMP + ") FROM " + SdgDatabase.TABLE_GRAIN_SITUATION + " GROUP BY " + SdgDatabase.COLUMN_CABLE_NO + ", " + "cable_name" + ")";
        Cursor cursor = db.rawQuery(query, null);
        return cursorToList(cursor);
    }
    
    @Override
    public List<GrainSituationEntity> getLatestGrainSituationByCable(int cableNo) {
        SQLiteDatabase db = database.getReadableDatabase();
        String query = "SELECT * FROM " + SdgDatabase.TABLE_GRAIN_SITUATION + " WHERE (" + SdgDatabase.COLUMN_CABLE_NO + ", " + "cable_name" + ", " + SdgDatabase.COLUMN_TIMESTAMP + ") IN " +
                "(SELECT " + SdgDatabase.COLUMN_CABLE_NO + ", " + "cable_name" + ", MAX(" + SdgDatabase.COLUMN_TIMESTAMP + ") FROM " + SdgDatabase.TABLE_GRAIN_SITUATION + " WHERE " + SdgDatabase.COLUMN_CABLE_NO + " = ? GROUP BY " + "cable_name" + ")";
        Cursor cursor = db.rawQuery(query, new String[]{String.valueOf(cableNo)});
        return cursorToList(cursor);
    }
    
    @Override
    public void deleteAll() {
        SQLiteDatabase db = database.getWritableDatabase();
        db.delete(SdgDatabase.TABLE_GRAIN_SITUATION, null, null);
    }
    
    @Override
    public int deleteDataBeforeTime(long timestamp) {
        SQLiteDatabase db = database.getWritableDatabase();
        return db.delete(SdgDatabase.TABLE_GRAIN_SITUATION, SdgDatabase.COLUMN_TIMESTAMP + " < ?", new String[]{String.valueOf(timestamp)});
    }
    
    @Override
    public int getCount() {
        SQLiteDatabase db = database.getReadableDatabase();
        Cursor cursor = db.rawQuery("SELECT COUNT(*) FROM " + SdgDatabase.TABLE_GRAIN_SITUATION, null);
        int count = 0;
        try {
            if (cursor.moveToFirst()) {
                count = cursor.getInt(0);
            }
        } finally {
            cursor.close();
        }
        return count;
    }
    
    @Override
    public int getCableCount() {
        SQLiteDatabase db = database.getReadableDatabase();
        Cursor cursor = db.rawQuery("SELECT COUNT(DISTINCT " + SdgDatabase.COLUMN_CABLE_NO + ") FROM " + SdgDatabase.TABLE_GRAIN_SITUATION, null);
        int count = 0;
        try {
            if (cursor.moveToFirst()) {
                count = cursor.getInt(0);
            }
        } finally {
            cursor.close();
        }
        return count;
    }
    
    @Override
    public List<Integer> getAllCableNumbers() {
        SQLiteDatabase db = database.getReadableDatabase();
        List<Integer> cableNumbers = new ArrayList<>();
        Cursor cursor = db.query(true, SdgDatabase.TABLE_GRAIN_SITUATION, new String[]{SdgDatabase.COLUMN_CABLE_NO}, null, null, null, null, SdgDatabase.COLUMN_CABLE_NO, null);
        try {
            while (cursor.moveToNext()) {
                int cableNoIndex = cursor.getColumnIndex(SdgDatabase.COLUMN_CABLE_NO);
                if (cableNoIndex != -1) {
                    cableNumbers.add(cursor.getInt(cableNoIndex));
                }
            }
        } finally {
            cursor.close();
        }
        return cableNumbers;
    }
    
    /**
     * 将实体对象转换为ContentValues
     * @param entity 实体对象
     * @return ContentValues
     */
    private ContentValues entityToContentValues(GrainSituationEntity entity) {
        ContentValues values = new ContentValues();
        values.put(SdgDatabase.COLUMN_CABLE_NO, entity.getCableNo());
        values.put(SdgDatabase.COLUMN_CABLE_NAME, entity.getCableName());
        values.put(SdgDatabase.COLUMN_TEMPERATURE, entity.getTemperature());
        values.put(SdgDatabase.COLUMN_HUMIDITY, entity.getHumidity());
        values.put(SdgDatabase.COLUMN_TIMESTAMP, entity.getTimestamp());
        values.put(SdgDatabase.COLUMN_TIME_STRING, entity.getTimeString());
        values.put(SdgDatabase.COLUMN_DEVICE_ID, entity.getDeviceId());
        values.put(SdgDatabase.COLUMN_REMARK, entity.getRemark());
        return values;
    }
    
    private List<GrainSituationEntity> cursorToList(Cursor cursor) {
        List<GrainSituationEntity> list = new ArrayList<>();
        if (cursor == null) return list;
        try {
            while(cursor.moveToNext()) {
                list.add(cursorToEntity(cursor));
            }
        } finally {
            cursor.close();
        }
        return list;
    }
    
    /**
     * 将Cursor转换为实体对象
     * @param cursor 数据库游标
     * @return 实体对象
     */
    private GrainSituationEntity cursorToEntity(Cursor cursor) {
        GrainSituationEntity entity = new GrainSituationEntity();

        int idIndex = cursor.getColumnIndex(SdgDatabase.COLUMN_ID);
        if (idIndex != -1) entity.setId(cursor.getLong(idIndex));

        int cableNoIndex = cursor.getColumnIndex(SdgDatabase.COLUMN_CABLE_NO);
        if (cableNoIndex != -1) entity.setCableNo(cursor.getInt(cableNoIndex));
        
        int cableNameIndex = cursor.getColumnIndex(SdgDatabase.COLUMN_CABLE_NAME);
        if (cableNameIndex != -1) entity.setCableName(cursor.getString(cableNameIndex));

        int tempIndex = cursor.getColumnIndex(SdgDatabase.COLUMN_TEMPERATURE);
        if (tempIndex != -1) entity.setTemperature(cursor.getFloat(tempIndex));
        
        int humidityIndex = cursor.getColumnIndex(SdgDatabase.COLUMN_HUMIDITY);
        if (humidityIndex != -1) entity.setHumidity(cursor.getFloat(humidityIndex));


        int timestampIndex = cursor.getColumnIndex(SdgDatabase.COLUMN_TIMESTAMP);
        if (timestampIndex != -1) entity.setTimestamp(cursor.getLong(timestampIndex));

        int timeStringIndex = cursor.getColumnIndex(SdgDatabase.COLUMN_TIME_STRING);
        if (timeStringIndex != -1) entity.setTimeString(cursor.getString(timeStringIndex));

        int deviceIdIndex = cursor.getColumnIndex(SdgDatabase.COLUMN_DEVICE_ID);
        if (deviceIdIndex != -1) entity.setDeviceId(cursor.getLong(deviceIdIndex));

        int remarkIndex = cursor.getColumnIndex(SdgDatabase.COLUMN_REMARK);
        if (remarkIndex != -1) entity.setRemark(cursor.getString(remarkIndex));

        return entity;
    }
} 